package huanji;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/** 数据库操作类 */
public class DataBase {
	static Connection con = null;
	static PreparedStatement pstmt = null;
	static Statement stmt = null;
	static ResultSet rs = null;
	static String sql = null;

	/** 连接数据库 */
	public static void connectDB() {
		try {
			System.out.println("正在连接数据库...");
			Class.forName("com.mysql.jdbc.Driver").newInstance(); // 创建MySQL数据库类实例
			con = DriverManager
					.getConnection(
							"jdbc:mysql://172.23.21.26:3306/huanjidata?useUnicode=true&characterEncoding=utf-8",
							"root", "123456");
			System.out.println("连接成功！");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/** 关闭连接 */
	public static void closeDB() {
		System.out.println("正在关闭数据库...");
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
			System.out.println("数据库连接已关闭！");

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 得到所有的base数据
	 * @param 
	 * @return
	 */
	public static List<Map<String, Object>> getAlltUserbase(){
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "select * from basefeaturecut";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**  
	 * 得到基础表的用户id  
	 **/
	public static List<Map<String, Object>> getOrderId(){
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "select row_no from basefeature";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 得到用户ID下的base数据
	 * @param byn
	 * @return
	 */
	public static Map<String, Object> getBaseByUserId(String UserId){
		Map<String, Object> list = new HashMap<>();
		String sql = "select * from basefeaturecut where row_no = "+UserId;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
			//	Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					list.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 得到用户ID下的所有有序的轨迹
	 * @param UserId
	 * @return
	 */
	public static List<Map<String, Object>> getOrderTrailByUserId(String UserId){
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "select * from terminalusedcut where ues_id = '"+ UserId +"' order by firstuse";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 利用source更新数据库，用于补全数据后会写到数据库
	 * @param source
	 */
	public static void upDateTable(List<Map<String, Object>> source){
		String sql = "UPDATE terminalusedcut "
				+ "SET "
				+ "ues_id =?, "
				+ "firstuse =?, "
				+ "finaluse =?, "
				+ "brand =?, "
				+ "model =?, "
				+ "intelligent =?, "
				+ "gps =?, "
				+ "camera =?, "
				+ "frontcamera =?, "
				+ "doublecard =?, "
				+ "pixel =?, "
				+ "cpu =?, "
				+ "cpuHz =?, "
				+ "screensize =?, "
				+ "ram =?, "
				+ "os =?, "
				+ "osversion =?, "
				+ "timetomarkwe =?, "
				+ "marketprice =?, "
				+ "nowprice =?, "
				+ "NFC =?, "
				+ "lte =?, "
				+ "mobiletype =? "
				+ "WHERE "
				+ "id =?";
		try {
			for(Map<String, Object> temp: source){
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, (int) temp.get("ues_id"));
				pstmt.setDate(2, (Date) temp.get("firstuse"));
				pstmt.setDate(3, (Date) temp.get("finaluse"));
				pstmt.setString(4, (String) temp.get("brand"));
				pstmt.setString(5, (String) temp.get("model"));
				pstmt.setString(6, (String) temp.get("intelligent"));
				pstmt.setString(7, (String) temp.get("gps"));
				pstmt.setString(8, (String) temp.get("camera"));
				pstmt.setString(9, (String) temp.get("frontcamera"));
				pstmt.setString(10, (String) temp.get("doublecard"));
				pstmt.setInt(11, (int) temp.get("pixel"));
				pstmt.setString(12, (String) temp.get("cpu"));
				pstmt.setInt(13, (int) temp.get("cpuHz"));
				pstmt.setFloat(14, (float) temp.get("screensize"));
				pstmt.setString(15, (String) temp.get("ram"));
				pstmt.setString(16, (String) temp.get("os"));
				pstmt.setFloat(17, (float) temp.get("osversion"));
				pstmt.setString(18, (String) temp.get("timetomarkwe"));
				pstmt.setInt(19, (int) temp.get("marketprice"));
				pstmt.setInt(20, (int) temp.get("nowprice"));
				pstmt.setString(21, (String) temp.get("NFC"));
				pstmt.setString(22, (String) temp.get("lte"));
				pstmt.setString(23, (String) temp.get("mobiletype"));
				pstmt.setInt(24, (int) temp.get("id"));
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 得到某一列的最大值
	 * @param columnName
	 * @return
	 */
	public static double getMaxValue (String columnName){
		String sql = "SELECT MAX("+columnName+") FROM basefeaturecut";
	//	String sql = "SELECT MAX("+columnName+") FROM basefeaturecut WHERE flow_1509 != ''";
		
		double length = 0;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			length = rs.getDouble("MAX("+columnName+")");
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return length;
	}
	/**
	 * 得到某一列的最小值
	 * @param columnName
	 * @return
	 */
	public static double getMinValue (String columnName){
		String sql = "SELECT MIN("+columnName+") FROM basefeaturecut";
		//String sql = "SELECT MIN("+columnName+") FROM basefeaturecut WHERE "+columnName+" != ''";
		double length = 0;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			length = rs.getDouble("MIN("+columnName+")");
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return length;
	}
	/**
	 * 得到用户ID下的time时间之前的所有手机类型
	 * @param time 格式为yyyy/mm/dd
	 * @return
	 */
	public static List<Map<String,Object>> getModelByUesIdTrougtTime (String UserId, String time) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "SELECT model FROM terminalused WHERE  ues_id = '"+UserId+"' and finaluse < STR_TO_DATE('"+time+"','%Y/%m/%d') and model != '' GROUP BY model" ;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 得到用户ID下的timestart时间到timeend时间间的所有手机类型
	 * @param time 格式为yyyy/mm/dd
	 * @return
	 */
	public static List<Map<String,Object>> getModelByUserIdTrougtBeTime (String UserId,String timeStart, String timeEnd) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "SELECT model FROM terminalused WHERE  ues_id = '"+UserId+"' and firstuse > STR_TO_DATE('"+timeStart+"','%Y/%m/%d') and finaluse < STR_TO_DATE('"+timeEnd+"','%Y/%m/%d')and model != '' GROUP BY model";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 得到用户ID下的某一时间time之前的所有有序的轨迹
	 * @param UserId
	 * @return
	 */
	public static List<Map<String, Object>> getOrderTrailByUserId(String UserId, String date){
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "select * from terminalused where ues_id = '"+UserId+"' and finaluse < STR_TO_DATE('"+date+"','%Y/%m/%d') order by firstuse";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 得到某一用户ID下的10月之前的所有数据
	 * @param UserId
	 * @return
	 */
	public static Map<String, Double> tengetBaseFeature (String UserId) {
		Map<String, Double> list = new HashMap<>();
		String sql = "SELECT "
				+ "row_no, "
				+ "flow_1505, "
				+ "flow_1506, "
				+ "flow_1507, "
				+ "flow_1508, "
				+ "flow_1509, "
				+ "arpu_1505, "
				+ "arpu_1506, "
				+ "arpu_1507, "
				+ "arpu_1508, "
				+ "arpu_1509, "
				+ "mou_1505, "
				+ "mou_1506, "
				+ "mou_1507, "
				+ "mou_1508, "
				+ "mou_1509 "
				+ "FROM "
				+ "basefeaturecut "
				+ "WHERE "
				+ "row_no = '"+UserId+"'";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				for (int i = 1; i <= columnCount; i++) {
					list.put(rsMeta.getColumnName(i), rs.getDouble(i));
				}
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 得到某一用户ID下的12月之前的所有数据
	 * @param UserId
	 * @return
	 */
	public static Map<String, Double> onegetBaseFeature (String UserId) {
		Map<String, Double> list = new HashMap<>();
		String sql = "SELECT "
				+ "row_no, "
				+ "flow_1508, "
				+ "flow_1509, "
				+ "flow_1510, "
				+ "flow_1511, "
				+ "flow_1512, "
				+ "arpu_1508, "
				+ "arpu_1509, "
				+ "arpu_1510, "
				+ "arpu_1511, "
				+ "arpu_1512, "
				+ "mou_1508, "
				+ "mou_1509, "
				+ "mou_1510, "
				+ "mou_1511, "
				+ "mou_1512 "
				+ "FROM "
				+ "basefeaturecut "
				+ "WHERE "
				+ "row_no = '"+UserId+"'";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				for (int i = 1; i <= columnCount; i++) {
					list.put(rsMeta.getColumnName(i), rs.getDouble(i));
				}
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 从base表中查询到排序后的结果
	 * @return
	 */
	public static List<Map<String, Object>> getOrdierList(String columnName,String tableName){
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "SELECT ues_id, "+columnName+" as value from "+tableName+" ORDER BY "+columnName;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 保存结果
	 * @param tableName
	 * @param source
	 */
	public static void addInfoToResult (String tableName, List<Map<String, Object>> source) {
		String SQL = "INSERT INTO "+tableName+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
				+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
				+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
				+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			for(Map<String, Object> temp: source){
				pstmt = con.prepareStatement(SQL);
				pstmt.setString(1, (String)temp.get("ues_id"));
				pstmt.setInt(2, (int)temp.get("label"));
				pstmt.setInt(3, (int)temp.get("finalNumber"));
				pstmt.setInt(4, (int)(double)temp.get("timeMax"));
				pstmt.setInt(5, (int)(double)temp.get("timeMin"));
				pstmt.setInt(6, (int)(double)temp.get("timeAverage"));
				pstmt.setLong(7, (long)temp.get("lastTimeLong"));
				pstmt.setString(8, (String) temp.get("lastBrand"));
				pstmt.setInt(9, (int)temp.get("lastCpuHz"));
				pstmt.setString(10, (String) temp.get("lastRam"));
				pstmt.setFloat(11, (float)temp.get("lastScreensize"));
				pstmt.setString(12, (String) temp.get("lastLte"));
				pstmt.setDouble(13, (double) temp.get("flowAdjoinDiff1"));
				pstmt.setDouble(14, (double) temp.get("flowAdjoinDiff2"));
				pstmt.setDouble(15, (double) temp.get("flowAdjoinDiff3"));
				pstmt.setDouble(16, (double) temp.get("flowAdjoinDiff4"));
				pstmt.setDouble(17, (double) temp.get("arpuAdjoinDiff1"));
				pstmt.setDouble(18, (double) temp.get("arpuAdjoinDiff2"));
				pstmt.setDouble(19, (double) temp.get("arpuAdjoinDiff3"));
				pstmt.setDouble(20, (double) temp.get("arpuAdjoinDiff4"));
				pstmt.setDouble(21, (double) temp.get("mouAdjoinDiff1"));
				pstmt.setDouble(22, (double) temp.get("mouAdjoinDiff2"));
				pstmt.setDouble(23, (double) temp.get("mouAdjoinDiff3"));
				pstmt.setDouble(24, (double) temp.get("mouAdjoinDiff4"));
				pstmt.setDouble(25, (double)temp.get("flowAverage"));
				pstmt.setDouble(26, (double)temp.get("flowVariance"));
				pstmt.setDouble(27, (double)temp.get("arpuAverage"));
				pstmt.setDouble(28, (double)temp.get("arpuVariance"));
				pstmt.setDouble(29, (double)temp.get("mouAverage"));
				pstmt.setDouble(30, (double)temp.get("mouVariance"));
				pstmt.setDouble(31, (double)temp.get("normalization1"));
				pstmt.setDouble(32, (double)temp.get("normalization2"));
				pstmt.setDouble(33, (double)temp.get("normalization3"));
				pstmt.setDouble(34, (double)temp.get("normalization4"));
				pstmt.setDouble(35, (double)temp.get("normalization5"));
				pstmt.setDouble(36, (double)temp.get("normalization6"));
				pstmt.setDouble(37, (double)temp.get("normalization7"));
				pstmt.setDouble(38, (double)temp.get("normalization8"));
				pstmt.setDouble(39, (double)temp.get("normalization9"));
				pstmt.setDouble(40, (double)temp.get("normalization10"));
				pstmt.setDouble(41, (double)temp.get("normalization11"));
				pstmt.setDouble(42, (double)temp.get("normalization12"));
				pstmt.setDouble(43, (double)temp.get("normalization13"));
				pstmt.setDouble(44, (double)temp.get("normalization14"));
				pstmt.setDouble(45, (double)temp.get("normalization15"));
				pstmt.setDouble(46, (double)(temp.get("order1")) );
				pstmt.setDouble(47, (double)temp.get("order2"));
				pstmt.setDouble(48, (double)temp.get("order3"));
				pstmt.setDouble(49, (double)temp.get("order4"));
				pstmt.setDouble(50, (double)temp.get("order5"));
				pstmt.setDouble(51, (double)temp.get("order6"));
				pstmt.setDouble(52, (double)temp.get("order7"));
				pstmt.setDouble(53, (double)temp.get("order8"));
				pstmt.setDouble(54, (double)temp.get("order9"));
				pstmt.setDouble(55, (double)temp.get("order10"));
				pstmt.setDouble(56, (double)temp.get("order11"));
				pstmt.setDouble(57, (double)temp.get("order12"));
				pstmt.setDouble(58, (double)temp.get("order13"));
				pstmt.setDouble(59, (double)temp.get("order14"));
				pstmt.setDouble(60, (double)temp.get("order15"));
				pstmt.executeUpdate();				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 更新结果表中的order值
	 * @param tableName
	 * @param order
	 * @param source
	 */
	public static void updateOrder (String tableName, String order, List<Map<String, Object>> source) {
		String sql = "UPDATE "+tableName+" SET "+ order +" =?  " + "WHERE " + "ues_id =?";
		try {
			for(Map<String, Object> temp: source){	
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, (int) temp.get(order));
				pstmt.setString(2, (String) temp.get("ues_id"));
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 得到某一用户ID下的10月之前的所有数据
	 * @param UserId
	 * @return
	 */
	public static List<Map<String, Object>> getBaseFeaturebefore10 () {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "SELECT "
				+ "row_no, "
				+ "flow_1505, "
				+ "flow_1506, "
				+ "flow_1507, "
				+ "flow_1508, "
				+ "flow_1509, "
				+ "arpu_1505, "
				+ "arpu_1506, "
				+ "arpu_1507, "
				+ "arpu_1508, "
				+ "arpu_1509, "
				+ "mou_1505, "
				+ "mou_1506, "
				+ "mou_1507, "
				+ "mou_1508, "
				+ "mou_1509 "
				+ "FROM "
				+ "basefeaturecut ";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 得到某一用户ID下的12月之前的所有数据
	 * @param UserId
	 * @return
	 */
	public static List<Map<String, Object>> getBaseFeaturebefore12 () {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "SELECT "
				+ "row_no, "
				+ "flow_1508, "
				+ "flow_1509, "
				+ "flow_1510, "
				+ "flow_1511, "
				+ "flow_1512, "
				+ "arpu_1508, "
				+ "arpu_1509, "
				+ "arpu_1510, "
				+ "arpu_1511, "
				+ "arpu_1512, "
				+ "mou_1508, "
				+ "mou_1509, "
				+ "mou_1510, "
				+ "mou_1511, "
				+ "mou_1512 "
				+ "FROM "
				+ "basefeaturecut ";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	public static void addTable(List<Map<String, Object>> testSet,
			String tableName) {
		String SQL = "INSERT INTO "
				+ tableName
				+ " (USER_ID,LABEL1,LABEL2) "
				+ "VALUES (?, ?, ?)";
		try {
			pstmt = con.prepareStatement(SQL);
			for (Map<String, Object> temp : testSet) {
				pstmt.setString(1, temp.get("use_id").toString());
				pstmt.setString(2, temp.get("label1").toString());
				pstmt.setString(3, temp.get("label2").toString());
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void addFeature(List<Map<String, Object>> testSet,
			String tableName) {
		String SQL = "INSERT INTO "
				+ tableName
				+ " (use_id,label,finalNumber,lastBrand,lastCpuHz,lastRam,lastScreensize,lastLte,lastTimeLong) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = con.prepareStatement(SQL);
			for (Map<String, Object> temp : testSet) {
				pstmt.setString(1, temp.get("use_id").toString());
				pstmt.setString(1, (String)temp.get("ues_id"));
				pstmt.setInt(2, (int)temp.get("label"));
				pstmt.setInt(3, (int)temp.get("finalNumber"));
				pstmt.setInt(4, (int)(double)temp.get("timeMax"));
				pstmt.setInt(5, (int)(double)temp.get("timeMin"));
				pstmt.setInt(6, (int)(double)temp.get("timeAverage"));
				pstmt.setLong(7, (long)temp.get("lastTimeLong"));
				pstmt.setString(8, (String) temp.get("lastBrand"));
				pstmt.setInt(9, (int)temp.get("lastCpuHz"));
				pstmt.setString(10, (String) temp.get("lastRam"));
				pstmt.setFloat(11, (float)temp.get("lastScreensize"));
				pstmt.setString(12, (String) temp.get("lastLte"));
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
